package com.example.mapp.buildSql;

import com.example.entity.supervision.PlanDict;
import com.example.entity.supervision.PublicRandom;
import org.apache.commons.lang3.StringUtils;

import java.util.List;
import java.util.Map;

/**
 * Created by simon on 2018/8/8.
 * 监管 监督检查所有复杂sql 都写在这里
 */
public class SupervisionMappSql {

    /**
     * 查询 计划目录
     * @param map
     * @return
     */
    public String queryPlanDict(PlanDict map){
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM ");
        sb.append(" plan_dict where 1=1 ");
        if(null!=map.getPdType() && StringUtils.isNotBlank(map.getPdType())){
            sb.append(" and pd_type=#{pdType} ");
        }
        if(null!=map.getId() && StringUtils.isNotBlank(map.getId()+"")){
            sb.append(" and id=#{id} ");
        }
        if(null!=map.getpPartyId() && StringUtils.isNotBlank(map.getpPartyId()+"")){
            sb.append(" and p_party_id=#{pPartyId} ");
        }
        if(null!=map.getPdName() && StringUtils.isNotBlank(map.getPdName())){
            String pdName = "'%"+map.getPdName()+"%'";
            sb.append(" and pd_name like "+pdName);
        }
        if(null!=map.getpStartDt() && StringUtils.isNotBlank(map.getpStartDt())){
            sb.append(" and p_start_dt >= #{pStartDt}");
        }
        if(null!=map.getpEndDt() && StringUtils.isNotBlank(map.getpEndDt())){
            sb.append(" and p_end_dt <= #{pEndDt}");
        }
        sb.append(" order by p_start_dt desc ");
        return sb.toString();
    }

    /**
     * 查询 计划明细
     * @param map
     * @return
     */
    public String queryPlanDetail(Map map){

        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT pd.*,pdc.*,pro.pro_count FROM ");
        sb.append(" plan_detail pd " +
                " left join plan_dict pdc on pdc.id=pd.plan_dict_id " +
        		"left join (select ppp.id,count(SUBSTRING_INDEX(SUBSTRING_INDEX(pc.contract_Name,',',help_topic_id+1),',',-1)) pro_count from party ppp inner join party_contract pc on ppp.id=pc.first_party or ppp.id=pc.second_party " 
				+ "INNER JOIN mysql.help_topic on 1=1 "
				+ "WHERE help_topic_id < LENGTH(pc.contract_Name)-LENGTH(REPLACE(pc.contract_Name,',',''))+1 "
				+ "group by ppp.id) pro on pro.id=pd.be_cked_party_id " +
                "  where 1=1 ");
        if(null!=map.get("planDictId") && StringUtils.isNotBlank(map.get("planDictId")+"")){
            sb.append(" and pd.plan_dict_id=#{planDictId} ");
        }
        if(null!=map.get("id") && StringUtils.isNotBlank(map.get("id")+"")){
            sb.append(" and pd.id=#{id} ");
        }
        if(null!=map.get("rsponUserName") && StringUtils.isNotBlank(map.get("rsponUserName")+"")){
            String rsponUserName = "'%"+map.get("rsponUserName")+"%'";
            sb.append(" and pd.rspon_user_name like "+rsponUserName);
        }
        if(null!=map.get("checkState") && StringUtils.isNotBlank(map.get("checkState")+"")){
            String checkStates = map.get("checkState")+"";
            if(!checkStates.contains(",")){
                sb.append(" and pd.check_state=#{checkState} ");
            }else{
                String [] checkStatess = checkStates.split(",");
                for (int i=0; i<checkStatess.length;i++) {
                    if(i==0){
                        sb.append(" and ( pd.check_state='"+checkStatess[i]+"' ");
                    }else if(i == (checkStatess.length-1)){
                        sb.append(" or pd.check_state='"+checkStatess[i]+"' )");
                    }
                    else{
                        sb.append(" or pd.check_state='"+checkStatess[i]+"' ");
                    }
                }
            }

        }
        if(null!=map.get("pdType") && StringUtils.isNotBlank(map.get("pdType")+"")){ //计划类型
            sb.append(" and pdc.pd_type=#{pdType} ");
        }
        if(null!=map.get("pPartyId") && StringUtils.isNotBlank(map.get("pPartyId")+"")){
            sb.append(" and pd.p_party_id=#{pPartyId} ");
        }
        if(null!=map.get("beCkedPartyName") && StringUtils.isNotBlank(map.get("beCkedPartyName")+"")){
            String beCkedPartyName = "'%"+map.get("beCkedPartyName")+"%'";
            sb.append(" and pd.be_cked_party_name like "+beCkedPartyName);
        }
        if(null!=map.get("startDate") && StringUtils.isNotBlank(map.get("startDate")+"")){
            sb.append(" and pd.check_date >= #{startDate}");
        }
        if(null!=map.get("endDate") && StringUtils.isNotBlank(map.get("endDate")+"")){
            sb.append(" and pd.check_date < #{endDate}");
        }
        sb.append(" ORDER BY pd.id DESC").append(" LIMIT "+map.get("start")+","+map.get("pageSize"));
        return sb.toString();
    }

    /**
     * 查询 计划明细总数
     * @param map
     * @return
     */
    public String queryPlanDetailTotal(Map map){

        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT count(pd.id) as total FROM ");
        sb.append(" plan_detail pd " +
                "  where 1=1 ");
        if(null!=map.get("planDictId") && StringUtils.isNotBlank(map.get("planDictId")+"")){
            sb.append(" and pd.plan_dict_id=#{planDictId} ");
        }
        if(null!=map.get("id") && StringUtils.isNotBlank(map.get("id")+"")){
            sb.append(" and pd.id=#{id} ");
        }
        if(null!=map.get("rsponUserName") && StringUtils.isNotBlank(map.get("rsponUserName")+"")){
            String rsponUserName = "'%"+map.get("rsponUserName")+"%'";
            sb.append(" and pd.rspon_user_name like "+rsponUserName);
        }
        if(null!=map.get("checkState") && StringUtils.isNotBlank(map.get("checkState")+"")){
            String checkStates = map.get("checkState")+"";
            if(!checkStates.contains(",")){
                sb.append(" and pd.check_state=#{checkState} ");
            }else{
                String [] checkStatess = checkStates.split(",");
                for (int i=0; i<checkStatess.length;i++) {
                    if(i==0){
                        sb.append(" and ( pd.check_state='"+checkStatess[i]+"' ");
                    }else if(i == (checkStatess.length-1)){
                        sb.append(" or pd.check_state='"+checkStatess[i]+"' )");
                    }
                    else{
                        sb.append(" or pd.check_state='"+checkStatess[i]+"' ");
                    }
                }
            }

        }
        if(null!=map.get("pPartyId") && StringUtils.isNotBlank(map.get("pPartyId")+"")){
            sb.append(" and pd.p_party_id=#{pPartyId} ");
        }
        if(null!=map.get("beCkedPartyName") && StringUtils.isNotBlank(map.get("beCkedPartyName")+"")){
            String beCkedPartyName = "'%"+map.get("beCkedPartyName")+"%'";
            sb.append(" and pd.be_cked_party_name like "+beCkedPartyName);
        }
        if(null!=map.get("startDate") && StringUtils.isNotBlank(map.get("startDate")+"")){
            sb.append(" and pd.check_date >= #{startDate}");
        }
        if(null!=map.get("endDate") && StringUtils.isNotBlank(map.get("endDate")+"")){
            sb.append(" and pd.check_date < #{endDate}");
        }
        return sb.toString();
    }

    /**
     * 查询检查项目
     * @param map
     * @return
     */
    public String queryCheckItem(Map map) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM ");
        sb.append(" check_item " +
                "  where 1=1 ");
        if (null != map.get("itemType") && StringUtils.isNotBlank(map.get("itemType") + "")) {
            sb.append(" and item_type=#{itemType}");
        }
        if (null != map.get("isUse") && StringUtils.isNotBlank(map.get("isUse") + "")) {
            sb.append(" and is_use=#{isUse}");
        }
        if (null != map.get("itemName") && StringUtils.isNotBlank(map.get("itemName") + "")) {
            sb.append(" and item_name=#{itemName}");
        }
        if (null != map.get("adDictId") && StringUtils.isNotBlank(map.get("adDictId") + "")) {
            sb.append(" and ad_dict_id=#{adDictId}");
        }
        return sb.toString();

    }

    public String queryPublicRandom(PublicRandom map) {
        StringBuffer sb = new StringBuffer();
        sb.append(" select * from public_random where pr_party_id = #{prPartyId}  ");

        if (null != map.getPrName() && StringUtils.isNotBlank(map.getPrName())) {
            String name = "'%"+ map.getPrName()+"%'";
            sb.append(" and pr_name like "+name);
        }
        if (null != map.getpStartDt() && StringUtils.isNotBlank(map.getpStartDt())) {
            sb.append(" and created_time >= #{pStartDt}");
        }
        if (null != map.getpEndDt() && StringUtils.isNotBlank(map.getpEndDt())) {
            sb.append(" and created_time <= #{pEndDt}");
        }
        sb.append(" order by created_time desc");
        return sb.toString();

    }

    public String queryProgramList(Map map) {
    	String partyId = map.get("partyId") + "";
    	String planDictId = map.get("planDictId") + "";
        StringBuffer sb = new StringBuffer();
        /*sb.append(" SELECT pro.* FROM ");
        sb.append(" (SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(a.contract_name,'|',b.help_topic_id+1),'|',-1) as program_name ");
        sb.append(" from party_contract a left join mysql.help_topic b ");
        sb.append(" on b.help_topic_id < (LENGTH(a.contract_name)-LENGTH(REPLACE(a.contract_name,'|',''))+1) ");
        sb.append(" where a.first_party = " + partyId + " or a.second_party = " + partyId + ") as PRO  ");
        sb.append(" where not EXISTS (select 1 from  ");
        sb.append(" (select SUBSTRING_INDEX(SUBSTRING_INDEX(used_pro.used_program_name,',',b.help_topic_id+1),',',-1) as used_program_name from  ");
        sb.append(" (select substring_index(substring_index(be_cked_party_name,')',1),'(',-1) as used_program_name from plan_detail  ");
        sb.append(" where be_cked_party_id = " + partyId + " and plan_dict_id=" + planDictId + " and be_cked_party_name like '%(%)') used_pro ");
        sb.append(" left join mysql.help_topic b  ");
        sb.append(" on b.help_topic_id < (LENGTH(used_pro.used_program_name)-LENGTH(REPLACE(used_pro.used_program_name,',',''))+1)) a where a.used_program_name=PRO.program_name) ");*/
        sb.append("select B.project_name AS programName from elevator.party_contract A left join party_contract_project B ON A.ID = B.contract_id where project_name is not null ");
        if(partyId != null){
            sb.append("and (first_party = #{partyId} or second_party = #{partyId})");
        }

        return sb.toString();
    }


    /**
     * 查询维保检查项
     */
    public String spotCheckItem(Map map) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM ");
        sb.append(" check_item where is_use='1' ");

        if (null != map.get("itemName") && StringUtils.isNotBlank(map.get("itemName") + "")) {
            sb.append(" and item_name=#{itemName}");
        }
        if (map.get("itemType") != null) {
            sb.append(" and item_type in ( ");
            // 进行In拼接
            List<String> types = (List<String>) map.get("itemType");
            for (int i = 0; i < types.size(); i++) {
                if (i == types.size() - 1) {
                    sb.append("'" + types.get(i) + "'");
                } else {
                    sb.append("'" + types.get(i) + "'" + ',');
                }
            }
            sb.append(")");
        }

        sb.append(" ORDER BY item_type,item_no");
        return sb.toString();
    }
}
